using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using QuanLyThuVien1.DTO;

namespace QuanLyThuVien1.DAO
{
    public class DOCGIA_DAO
    {
        public void themDG(DOCGIA d)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "insert into DOCGIA values('" + d.MADG + "', '" + d.TENDG + "','" + d.PASSW + " ',' " + d.EMAIL + "',";
            connecstr += " '" + d.DIACHI + "','" + d.DIENTHOAI + "','" + d.NGAYSINH + "','" + d.PHAI + "','" + d.SOCMND + "','" + d.LOAIDG + "',";
            connecstr += " '" + d.NGUOILAPTHE+"','"+d.NGAYLAP+"','" + d.TIENNO + "')";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.ExecuteNonQuery();
            connect.Close();
 
        }
        public void xoaDG(string maDG)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "delete from DOCGIA where maDocGia = '" + maDG + "' ";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.ExecuteNonQuery();
            connect.Close();
        }
        public DOCGIA timDG(string maDG)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from DOCGIA where maDocGia = '" + maDG + "'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            OleDbDataReader reader;
            reader = command.ExecuteReader();
            DOCGIA a = new DOCGIA();
            while (reader.Read())
            {
                a.MADG = reader.GetString(0);
                a.TENDG = reader.GetString(1);
                a.PASSW = reader.GetString(2);
                a.EMAIL = reader.GetString(3);
                a.DIACHI = reader.GetString(4);
                a.DIENTHOAI = reader.GetString(5);
                a.NGAYSINH = reader.GetDateTime(6).Date;
                a.PHAI = reader.GetString(7);
                a.SOCMND = reader.GetInt32(8);
                a.LOAIDG = reader.GetString(9);
                a.NGUOILAPTHE = reader.GetString(10);
                a.NGAYLAP = reader.GetDateTime(11).Date;
                a.TIENNO = reader.GetInt32(12);
 
            }
            connect.Close();
            return a;

        }
        public void capNhat(DOCGIA d,  string aid)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "update DOCGIA set tenDocGia = '" + d.TENDG + "',pass = '" + d.PASSW + "',dienThoai = '" + d.DIENTHOAI + "',";
            connecstr += " diaChi = '" + d.DIACHI + "',ngaySinh = '" + d.NGAYSINH + "',email = '" + d.EMAIL + "',soCMND = '" + d.SOCMND+"',phai = '" + d.PHAI+"'";
            connecstr += "where maDocGia = '"+ aid +"'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.ExecuteNonQuery();
            connect.Close();

        }
        public void CapNhatThongTinDG(DOCGIA d, string maDG,string pass)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = " UPDATE DOCGIA set pass = '" + d.PASSW + "', dienThoai = '" + d.DIENTHOAI + "',";
            connecstr += " diaChi = '" + d.DIACHI + "',email = '" + d.EMAIL + "'";
            connecstr += " where maDocGia = '" + maDG + "' and  pass = '" + pass+"'";
           
            OleDbCommand command = new OleDbCommand(connecstr, connect);
           command.ExecuteNonQuery();
            connect.Close();

        }
        public string LayPassCuaDG(string maDG)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select pass from DOCGIA where maDocGia = " + maDG;
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            adapter.Fill(d);
            connect.Close();
            return d.Rows[0][0].ToString();
        }
        public DataTable LayDanhSachDG()
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from DOCGIA";
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            adapter.Fill(d);
            connect.Close();
            return d;
        }
        public DataTable LayThongTin1DocGia(string pass, string maDG)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select* from DOCGIA where maDocGia = '"+maDG+"'and pass=  '"+ pass+"'" ;
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            adapter.Fill(d);
            connect.Close();
            return d;
        }
        public int tienPhat(string maDG)
        {
            int soTien = 0;
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select tienPhat from DOCGIA where maDocGia = '" + maDG + "'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            OleDbDataReader Reader =  command.ExecuteReader();
            while (Reader.Read())
            {
                soTien = Reader.GetInt32(0);
            }
            connect.Close();
            return soTien;
        }
        public void CapNhatTienPhat(string maDG,int soTien)
        {
            int tongTien = 0;
            tongTien = tienPhat(maDG) + soTien;
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "update DOCGIA set tienPhat = ? where maDocGia = '" + maDG + "'";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.Parameters.Add("tienPhat",OleDbType.Integer);
            command.Parameters["tienPhat"].Value = tongTien ;
            command.ExecuteNonQuery();
            connect.Close();
        }
        public bool KiemTraDG(string maDG)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from DOCGIA where maDocGia = '" + maDG + "'";
            DataTable d = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr,connect);
            adapter.Fill(d);
            if (d.Rows.Count > 0)
                return true;
            return false;
        }
        public string LayTenDG(string maDG)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select tenDocGia from DOCGIA where maDocGia = '" + maDG + "'";
            OleDbDataAdapter a = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            a.Fill(d);
            connect.Close();
            return d.Rows[0][0].ToString();
        }
        public DataTable LayDanhSachDGNoTien()
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select tenDocGia,tienPhat from DOCGIA where tienPhat > 0";
            OleDbDataAdapter a = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            a.Fill(d);
            connect.Close();
            return d;
        }
        public string TinhTongTienNo()
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select sum(tienPhat) from DOCGIA";
            OleDbDataAdapter a = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            a.Fill(d);
            connect.Close();
            return d.Rows[0][0].ToString();
        }
        public DateTime LayNgayLamThe(string maDG)
        {
             OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select ngayLap from DOCGIA where maDocGia = '"+maDG+"'";
            OleDbDataAdapter a = new OleDbDataAdapter(connecstr, connect);
            DataTable d = new DataTable();
            a.Fill(d);
            connect.Close();
            return DateTime.Parse(d.Rows[0][0].ToString());
        }
        
    }
}
